package cn.com.acca.ma.dao.impl;

import cn.com.acca.ma.dao.Real4TransactionConditionDao;
import cn.com.acca.ma.model.Real4TransactionCondition;
import cn.com.acca.ma.model.RealTransactionCondition;
import cn.com.acca.ma.pojo.Real4TransactionConditionPojo;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Real4TransactionConditionDaoImpl extends BaseDaoImpl<Real4TransactionConditionDao> implements
    Real4TransactionConditionDao {

    public Real4TransactionConditionDaoImpl() {
        super();
    }

    /**
     * 向real4_transaction_condition表中写数据。
     * ma120NotDecreasing为1时表示判断120均线是否不单调递减，ma120NotDecreasingDateNumber表示120日均线多少天内不单调递减；
     * ma250NotDecreasing为1时表示判断250均线是否不单调递减，ma250NotDecreasingDateNumber表示250日均线多少天内不单调递减。
     * ma120NotIncreasing为1时表示判断120均线是否不单调递增，ma120NotIncreasingDateNumber表示120日均线多少天内不单调递增；
     * ma250NotIncreasing为1时表示判断250均线是否不单调递增，ma250NotIncreasingDateNumber表示250日均线多少天内不单调递增。
     * @param xrBeginDate
     * @param date
     * @param lessThanPercentage
     * @param moreThanPercentage
     * @param closePriceStart
     * @param closePriceEnd
     * @param ma120NotDecreasing
     * @param ma120NotDecreasingDateNumber
     * @param ma250NotDecreasing
     * @param ma250NotDecreasingDateNumber
     * @param ma120NotIncreasing
     * @param ma120NotIncreasingDateNumber
     * @param ma250NotIncreasing
     * @param ma250NotIncreasingDateNumber
     */
    @Override
    public void writeReal4TransactionCondition(String xrBeginDate, String date, /*Integer lessThanPercentage,
                                              Integer moreThanPercentage, Double closePriceStart, Double closePriceEnd,*/
                                              Integer ma120NotDecreasing, Integer ma120NotDecreasingDateNumber,
                                              Integer ma250NotDecreasing, Integer ma250NotDecreasingDateNumber,
                                              Integer ma120NotIncreasing, Integer ma120NotIncreasingDateNumber,
                                              Integer ma250NotIncreasing, Integer ma250NotIncreasingDateNumber) {
        logger.info("开始向real4_transaction_condition表中写数据，日期为【" + date + "】");

        String sql = "{call PKG_REAL4_TRANSACTION.write_real4_transac_cond('" + xrBeginDate + "', '" + date + "', "
//                + lessThanPercentage + ", " + moreThanPercentage + ", " + closePriceStart + ", " + closePriceEnd + ", "
                + ma120NotDecreasing + ", " + ma120NotDecreasingDateNumber + ", "
                + ma250NotDecreasing + ", " + ma250NotDecreasingDateNumber + ", "
                + ma120NotIncreasing + ", " + ma120NotIncreasingDateNumber + ", "
                + ma250NotIncreasing + ", " + ma250NotIncreasingDateNumber + ")}";

        doSQLInTransaction(sql);
    }

    /**
     * 清空real4_transaction_condition表中的数据
     */
    @Override
    public void truncateTableReal4TransactionCondition() {
        logger.info("开始清空real4_transaction_condition表中的数据");

        String sql = "truncate table real4_transaction_condition";

        doSQLInTransaction(sql);
    }

    /**
     * 根据stock_code字段查找Real4TransactionCondition类型对象
     * @param stockCode
     * @return
     */
    @Override
    public Real4TransactionCondition findByStockCode(String stockCode) {
        logger.info("根据stock_code字段查找Real4TransactionCondition类型对象");

        String sql = "select * "
            + "from real4_transaction_condition t "
            + "where t.stock_code ='" + stockCode + "'";

        List list = doSQLQueryInTransaction(sql, null, Real4TransactionCondition.class);
        if (null == list || list.size() == 0){
            logger.warn("表real4_transaction_condition中没有code为【" + stockCode + "】的记录，这支"
                + "股票应在已经停牌了");
            return null;
        } else {
            return (Real4TransactionCondition) doSQLQueryInTransaction(sql, null, Real4TransactionCondition.class).get(0);
        }
    }

    /**
     * 根据条件查找记录：120日和250日均线不单调递减
     * 如果allFilterCondition为true，则使用所有过滤条件；否则只是有价格区间条件
     * @return
     */
    @Override
    public List<Real4TransactionCondition> findByCondition(boolean allFilterCondition) {
        logger.info("根据条件查找记录：120日和250日均线不单调递减");

        String sql;
        if (allFilterCondition){
            sql = "select * from real4_transaction_condition t " +
                    "where t.is_ma_1_2_0_not_decreasing=1 and t.is_ma_2_5_0_not_decreasing=1 " +
                    "and t.stock_code not in(select t1.stock_code from real4_stock_transaction_record t1 " +
                    "where t1.direction=1 and t1.sell_date is null) " +
                    "union " +
                    "select * from real4_transaction_condition t " +
                    "where t.is_ma_1_2_0_not_increasing=1 and t.is_ma_2_5_0_not_increasing=1 " +
                    "and t.stock_code not in(select t1.stock_code from real4_stock_transaction_record t1 " +
                    "where t1.direction=-1 and t1.buy_date is null)";
        } else {
            sql = "select * from real4_transaction_condition t " +
                    "where t.stock_code not in(select t1.stock_code from real4_stock_transaction_record t1 " +
                    "where t1.direction=1 and t1.sell_date is null) " +
                    "union " +
                    "select * from real4_transaction_condition t " +
                    "where t.stock_code not in(select t1.stock_code from real4_stock_transaction_record t1 " +
                    "where t1.direction=-1 and t1.buy_date is null)";
        }

        return (List<Real4TransactionCondition>) doSQLQueryInTransaction(sql, null, Real4TransactionCondition.class);
    }

    /**
     * 这是一个优化了的sql，返回很多列，在下面的程序中会使用到
     * 根据条件查找记录：120日和250日均线不单调递减
     * 如果allFilterCondition为true，则使用所有过滤条件；否则只是有价格区间条件
     * @param allFilterCondition
     * @param date
     * @return
     */
    @Override
    public List<Real4TransactionConditionPojo> findByConditionForReal4TransactionConditionPojo(boolean allFilterCondition, String date) {
        logger.info("根据条件查找记录：120日和250日均线不单调递减");

        String sql;
        if (allFilterCondition){
            sql = "select t.stock_code stockCode, si_.url_param urlParam, to_date(:date, 'yyyy-mm-dd') transactionDate, stda.open_price openPriceFromDB, " +
                        "stda.close_price closePriceFromDB, stda.highest_price highestPriceFromDB, stda.lowest_price lowestPriceFromDB, " +
                        "(select t2.close_price from ( " +
                        "select t1.close_price from stock_transaction_data_all t1 where t1.code_=t.stock_code and t1.date_<to_date(:date, 'yyyy-mm-dd') order by t1.date_ desc ) t2 " +
                        "where rownum<=1) previousClosePrice, " +
                        "(select t10.ma5 from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekMa5, " +
//                        "(select t3.ma5 from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) lastClosePrice, " +
//                        "(select t3.close_price from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekClosePrice, " +
                        "(select t10.close_price from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekClosePrice, " +
//                        "(select t3.ma5 from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekMa5, " +
                        "(select t10.ma5 from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekMa5, " +
//                        "(select t3.begin_date from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekBeginDate, " +
//                        "(select t3.end_date from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekEndDate, " +
                        "(select t10.begin_date from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekBeginDate, " +
                        "(select t10.end_date from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekEndDate, " +
                        "(select t8.ha_open_price from ( " +
                        "select t7.* from ( " +
                        "select t6.* from ( " +
                        "select t5.* from stock_transaction_data_all t5 where t5.code_=t.stock_code and t5.date_<=to_date(:date, 'yyyy-mm-dd') order by t5.date_ desc) t6 " +
                        "where rownum<=2) t7 order by t7.date_) t8 " +
                        "where rownum<=1) lastHaOpenPrice, " +
                        "(select t8.ha_close_price from ( " +
                        "select t7.* from ( " +
                        "select t6.* from ( " +
                        "select t5.* from stock_transaction_data_all t5 where t5.code_=t.stock_code and t5.date_<=to_date(:date, 'yyyy-mm-dd') order by t5.date_ desc) t6 " +
                        "where rownum<=2) t7 order by t7.date_) t8 " +
                        "where rownum<=1) lastHaClosePrice, " +
                        "(select max(t10.highest_price) " +
                        "from (select * from stock_transaction_data_all t9 where t9.code_=t.stock_code and t9.date_<to_date(:date, 'yyyy-mm-dd') " +
                        "order by t9.date_ desc) t10 " +
                        "where rownum<=8) highestPriceWith8Day, " +
                        "(select min(t10.lowest_price) " +
                        "from (select * from stock_transaction_data_all t9 where t9.code_=t.stock_code and t9.date_<to_date(:date, 'yyyy-mm-dd') " +
                        "order by t9.date_ desc) t10 " +
                        "where rownum<=8) lowestPriceWith8Day, " +
                        "t.macd_gold_cross_close_price macdGoldCrossClosePrice, t.macd_dead_cross_close_price macdDeadCrossClosePrice, " +
                        "t.c_p_ma_5_g_c_close_price cPMa5GCClosePrice, t.c_p_ma_5_d_c_close_price cPMa5DCClosePrice, " +
                        "t.h_k_a_up_h_k_a_open_price hKAUpHKAOpenPrice, t.h_k_a_down_h_k_a_open_price hKADownHKAOpenPrice, " +
                        "t.kd_gold_cross_rsv kdGoldCrossRsv, t.kd_dead_cross_rsv kdDeadCrossRsv, si_.name_ stockName, si_.mark mark " +
                    "from real4_transaction_condition t " +
                    "join stock_info si_ on si_.code_=t.stock_code " +
                    "join stock_transaction_data_all stda on stda.code_=t.stock_code and stda.date_=to_date(:date, 'yyyy-mm-dd') " +
                    "where t.is_ma_1_2_0_not_decreasing=1 and t.is_ma_2_5_0_not_decreasing=1 " +
                        "and t.stock_code not in(select t1.stock_code from real4_stock_transaction_record t1 " +
                        "where t1.buy_date is null or t1.sell_date is null) " +
                    "union " +
                    "select t.stock_code stockCode, si_.url_param urlParam, to_date(:date, 'yyyy-mm-dd') transactionDate, stda.open_price openPriceFromDB, " +
                        "stda.close_price closePriceFromDB, stda.highest_price highestPriceFromDB, stda.lowest_price lowestPriceFromDB, " +
                        "(select t2.close_price from ( " +
                        "select t1.close_price from stock_transaction_data_all t1 where t1.code_=t.stock_code and t1.date_<to_date(:date, 'yyyy-mm-dd') order by t1.date_ desc ) t2 " +
                        "where rownum<=1) previousClosePrice, " +
                        "(select t10.ma5 from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekMa5, " +
//                        "(select t3.ma5 from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) lastClosePrice, " +
//                        "(select t3.close_price from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekClosePrice, " +
                        "(select t10.close_price from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekClosePrice, " +
//                        "(select t3.ma5 from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekMa5, " +
                        "(select t10.ma5 from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekMa5, " +
//                        "(select t3.begin_date from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekBeginDate, " +
//                        "(select t3.end_date from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekEndDate, " +
                        "(select t10.begin_date from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekBeginDate, " +
                        "(select t10.end_date from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekEndDate, " +
                        "(select t8.ha_open_price from ( " +
                        "select t7.* from ( " +
                        "select t6.* from ( " +
                        "select t5.* from stock_transaction_data_all t5 where t5.code_=t.stock_code and t5.date_<=to_date(:date, 'yyyy-mm-dd') order by t5.date_ desc) t6 " +
                        "where rownum<=2) t7 order by t7.date_) t8 " +
                        "where rownum<=1) lastHaOpenPrice, " +
                        "(select t8.ha_close_price from ( " +
                        "select t7.* from ( " +
                        "select t6.* from ( " +
                        "select t5.* from stock_transaction_data_all t5 where t5.code_=t.stock_code and t5.date_<=to_date(:date, 'yyyy-mm-dd') order by t5.date_ desc) t6 " +
                        "where rownum<=2) t7 order by t7.date_) t8 " +
                        "where rownum<=1) lastHaClosePrice, " +
                        "(select max(t10.highest_price) " +
                        "from (select * from stock_transaction_data_all t9 where t9.code_=t.stock_code and t9.date_<to_date(:date, 'yyyy-mm-dd') " +
                        "order by t9.date_ desc) t10 " +
                        "where rownum<=8) highestPriceWith8Day, " +
                        "(select min(t10.lowest_price) " +
                        "from (select * from stock_transaction_data_all t9 where t9.code_=t.stock_code and t9.date_<to_date(:date, 'yyyy-mm-dd') " +
                        "order by t9.date_ desc) t10 " +
                        "where rownum<=8) lowestPriceWith8Day, " +
                        "t.macd_gold_cross_close_price macdGoldCrossClosePrice, t.macd_dead_cross_close_price macdDeadCrossClosePrice, " +
                        "t.c_p_ma_5_g_c_close_price cPMa5GCClosePrice, t.c_p_ma_5_d_c_close_price cPMa5DCClosePrice, " +
                        "t.h_k_a_up_h_k_a_open_price hKAUpHKAOpenPrice, t.h_k_a_down_h_k_a_open_price hKADownHKAOpenPrice, " +
                        "t.kd_gold_cross_rsv kdGoldCrossRsv, t.kd_dead_cross_rsv kdDeadCrossRsv, si_.name_ stockName, si_.mark mark " +
                    "from real4_transaction_condition t " +
                    "join stock_info si_ on si_.code_=t.stock_code " +
                    "join stock_transaction_data_all stda on stda.code_=t.stock_code and stda.date_=to_date(:date, 'yyyy-mm-dd') " +
                    "where t.is_ma_1_2_0_not_decreasing=1 and t.is_ma_2_5_0_not_decreasing=1 " +
                        "and t.stock_code not in(select t1.stock_code from real4_stock_transaction_record t1 " +
                        "where t1.buy_date is null or t1.sell_date is null)";
        } else {
            sql = "select t.stock_code stockCode, si_.url_param urlParam, to_date(:date, 'yyyy-mm-dd') transactionDate, stda.open_price openPriceFromDB, " +
                        "stda.close_price closePriceFromDB, stda.highest_price highestPriceFromDB, stda.lowest_price lowestPriceFromDB, " +
                        "(select t2.close_price from (" +
                        "select t1.close_price from stock_transaction_data_all t1 where t1.code_=t.stock_code and t1.date_<to_date(:date, 'yyyy-mm-dd') order by t1.date_ desc ) t2 " +
                        "where rownum<=1) previousClosePrice, " +
                        "(select t10.ma5 from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekMa5, " +
//                        "(select t3.ma5 from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) lastClosePrice, " +
//                        "(select t3.close_price from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekClosePrice, " +
                        "(select t10.close_price from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekClosePrice, " +
//                        "(select t3.ma5 from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekMa5, " +
                        "(select t10.ma5 from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekMa5, " +
//                        "(select t3.begin_date from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekBeginDate, " +
//                        "(select t3.end_date from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekEndDate, " +
                        "(select t10.begin_date from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekBeginDate, " +
                        "(select t10.end_date from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekEndDate, " +
                        "(select t8.ha_open_price from ( " +
                        "select t7.* from ( " +
                        "select t6.* from ( " +
                        "select t5.* from stock_transaction_data_all t5 where t5.code_=t.stock_code and t5.date_<=to_date(:date, 'yyyy-mm-dd') order by t5.date_ desc) t6 " +
                        "where rownum<=2) t7 order by t7.date_) t8 " +
                        "where rownum<=1) lastHaOpenPrice, " +
                        "(select t8.ha_close_price from ( " +
                        "select t7.* from ( " +
                        "select t6.* from ( " +
                        "select t5.* from stock_transaction_data_all t5 where t5.code_=t.stock_code and t5.date_<=to_date(:date, 'yyyy-mm-dd') order by t5.date_ desc) t6 " +
                        "where rownum<=2) t7 order by t7.date_) t8 " +
                        "where rownum<=1) lastHaClosePrice, " +
                        "(select max(t10.highest_price) " +
                        "from (select * from stock_transaction_data_all t9 where t9.code_=t.stock_code and t9.date_<to_date(:date, 'yyyy-mm-dd') " +
                        "order by t9.date_ desc) t10 " +
                        "where rownum<=8) highestPriceWith8Day, " +
                        "(select min(t10.lowest_price) " +
                        "from (select * from stock_transaction_data_all t9 where t9.code_=t.stock_code and t9.date_<to_date(:date, 'yyyy-mm-dd') " +
                        "order by t9.date_ desc) t10 " +
                        "where rownum<=8) lowestPriceWith8Day, " +
                        "t.macd_gold_cross_close_price macdGoldCrossClosePrice, t.macd_dead_cross_close_price macdDeadCrossClosePrice, " +
                        "t.c_p_ma_5_g_c_close_price cPMa5GCClosePrice, t.c_p_ma_5_d_c_close_price cPMa5DCClosePrice, " +
                        "t.h_k_a_up_h_k_a_open_price hKAUpHKAOpenPrice, t.h_k_a_down_h_k_a_open_price hKADownHKAOpenPrice, " +
                        "t.kd_gold_cross_rsv kdGoldCrossRsv, t.kd_dead_cross_rsv kdDeadCrossRsv, si_.name_ stockName, si_.mark mark " +
                    "from real4_transaction_condition t " +
                    "join stock_info si_ on si_.code_=t.stock_code " +
                    "join stock_transaction_data_all stda on stda.code_=t.stock_code and stda.date_=to_date(:date, 'yyyy-mm-dd') " +
                        "where t.stock_code not in(select t1.stock_code from real4_stock_transaction_record t1 " +
                        "where t1.buy_date is null or t1.sell_date is null) " +
                    "union " +
                    "select t.stock_code stockCode, si_.url_param urlParam, to_date(:date, 'yyyy-mm-dd') transactionDate, stda.open_price openPriceFromDB, " +
                        "stda.close_price closePriceFromDB, stda.highest_price highestPriceFromDB, stda.lowest_price lowestPriceFromDB, " +
                        "(select t2.close_price from (" +
                        "select t1.close_price from stock_transaction_data_all t1 where t1.code_=t.stock_code and t1.date_<to_date(:date, 'yyyy-mm-dd') order by t1.date_ desc ) t2 " +
                        "where rownum<=1) previousClosePrice, " +
                        "(select t10.ma5 from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekMa5, " +
//                        "(select t3.ma5 from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) lastClosePrice, " +
//                        "(select t3.close_price from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekClosePrice, " +
                        "(select t10.close_price from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekClosePrice, " +
//                        "(select t3.ma5 from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekMa5, " +
                        "(select t10.ma5 from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekMa5, " +
//                        "(select t3.begin_date from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekBeginDate, " +
//                        "(select t3.end_date from stock_week t3 where t3.code_=t.stock_code and to_date(:date, 'yyyy-mm-dd') between t3.begin_date and t3.end_date) weekEndDate, " +
                        "(select t10.begin_date from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekBeginDate, " +
                        "(select t10.end_date from (select * from stock_week t3 where t3.code_=t.stock_code and t3.end_date<to_date(:date, 'yyyy-mm-dd') order by t3.end_date desc) t10 where rownum<=1) weekEndDate, " +
                        "(select t8.ha_open_price from ( " +
                        "select t7.* from ( " +
                        "select t6.* from ( " +
                        "select t5.* from stock_transaction_data_all t5 where t5.code_=t.stock_code and t5.date_<=to_date(:date, 'yyyy-mm-dd') order by t5.date_ desc) t6 " +
                        "where rownum<=2) t7 order by t7.date_) t8 " +
                        "where rownum<=1) lastHaOpenPrice, " +
                        "(select t8.ha_close_price from ( " +
                        "select t7.* from ( " +
                        "select t6.* from ( " +
                        "select t5.* from stock_transaction_data_all t5 where t5.code_=t.stock_code and t5.date_<=to_date(:date, 'yyyy-mm-dd') order by t5.date_ desc) t6 " +
                        "where rownum<=2) t7 order by t7.date_) t8 " +
                        "where rownum<=1) lastHaClosePrice, " +
                        "(select max(t10.highest_price) " +
                        "from (select * from stock_transaction_data_all t9 where t9.code_=t.stock_code and t9.date_<to_date(:date, 'yyyy-mm-dd') " +
                        "order by t9.date_ desc) t10 " +
                        "where rownum<=8) highestPriceWith8Day, " +
                        "(select min(t10.lowest_price) " +
                        "from (select * from stock_transaction_data_all t9 where t9.code_=t.stock_code and t9.date_<to_date(:date, 'yyyy-mm-dd') " +
                        "order by t9.date_ desc) t10 " +
                        "where rownum<=8) lowestPriceWith8Day, " +
                        "t.macd_gold_cross_close_price macdGoldCrossClosePrice, t.macd_dead_cross_close_price macdDeadCrossClosePrice, " +
                        "t.c_p_ma_5_g_c_close_price cPMa5GCClosePrice, t.c_p_ma_5_d_c_close_price cPMa5DCClosePrice, " +
                        "t.h_k_a_up_h_k_a_open_price hKAUpHKAOpenPrice, t.h_k_a_down_h_k_a_open_price hKADownHKAOpenPrice, " +
                        "t.kd_gold_cross_rsv kdGoldCrossRsv, t.kd_dead_cross_rsv kdDeadCrossRsv, si_.name_ stockName, si_.mark mark " +
                    "from real4_transaction_condition t " +
                    "join stock_info si_ on si_.code_=t.stock_code " +
                    "join stock_transaction_data_all stda on stda.code_=t.stock_code and stda.date_=to_date(:date, 'yyyy-mm-dd') " +
                        "where t.stock_code not in(select t1.stock_code from real4_stock_transaction_record t1 " +
                        "where t1.buy_date is null or t1.sell_date is null)";
        }

        Map<String, Object> map = new HashMap();
        map.put("date", date);
        return (List<Real4TransactionConditionPojo>) doSQLQueryInTransaction(sql, map, Real4TransactionConditionPojo.class);
    }
}
